package org.compiere.process;

import java.awt.Color;
import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.CellFormat;
import jxl.Workbook;
import jxl.biff.FontRecord;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.apache.commons.net.ntp.TimeStamp;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.jboss.util.propertyeditor.BigDecimalEditor;
import org.joda.time.DateTime;

import com.lowagie.text.Font;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class MM_RestOfMaterials extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	private String getSQlresult = null;
	private BigDecimal Warehouse = null; 
	private BigDecimal Locator = null;
	private BigDecimal Werk = null;
	
	private static CLogger s_log = CLogger.getCLogger(BalanceSheets.class);
	/** */
	private String path = "";

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null);
			else if (name.equals("Warehouse")){ 
				Warehouse = (BigDecimal) para[i].getParameter();
			}else if (name.equals("Werk")) 
				Werk = (BigDecimal) para[i].getParameter();
			else if (name.equals("Locator")){ 
				Locator = (BigDecimal) para[i].getParameter();
			}else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		
		path = Util.localFilePath;
	}

	@Override
	protected String doIt() throws Exception {

		Label label;
		Number number;
		
		String linux = "";
		
		if(Env.isWindows())
			 linux = "";
		else
			linux = "/";

		String templateName = "MM_RestOfMaterials_tmpl.xls";
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate") + " " + templateName);

		File templateCopy = new File(Util.localFilePath + linux + "MM_RestOfMaterials.xls");
		File temporaryFile = entry.getFile(Util.localFilePath + linux + templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		try {
			tableBook = Workbook.getWorkbook(temporaryFile);
			copy = Workbook.createWorkbook(templateCopy, tableBook);
			sheet = copy.getSheet(0);
		} catch (Exception e) {
			ADialog.error(999, null, "Error tableBook. " + e.getMessage());
			return "Error tableBook.";
		}

		WritableFont arial10ptBold = new WritableFont(WritableFont.TIMES, 11, WritableFont.NO_BOLD);
		WritableCellFormat vsFloat = null;
		vsFloat = new WritableCellFormat(arial10ptBold,NumberFormats.THOUSANDS_FLOAT); 
		vsFloat.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		vsFloat.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		vsFloat.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		vsFloat.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		vsFloat.setAlignment(Alignment.RIGHT);
		vsFloat.setVerticalAlignment(VerticalAlignment.CENTRE);
		vsFloat.setWrap(true);
		vsFloat.setShrinkToFit(true);
		
		WritableFont arial10ptBold2 = new WritableFont(WritableFont.TIMES, 11, WritableFont.NO_BOLD);
		WritableCellFormat vsInteter = null;
		vsInteter = new WritableCellFormat(arial10ptBold2,NumberFormats.INTEGER); 
		vsInteter.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		vsInteter.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		vsInteter.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		vsInteter.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		vsInteter.setAlignment(Alignment.RIGHT);
		vsInteter.setVerticalAlignment(VerticalAlignment.CENTRE);
		vsInteter.setWrap(true);
		vsInteter.setShrinkToFit(true);
		
		WritableCellFormat vsLabel = null;
		vsLabel = new WritableCellFormat(arial10ptBold,NumberFormats.THOUSANDS_FLOAT); 
		vsLabel.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		vsLabel.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		vsLabel.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		vsLabel.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		vsLabel.setAlignment(Alignment.CENTRE);
		vsLabel.setVerticalAlignment(VerticalAlignment.CENTRE);
		vsLabel.setWrap(true);
		vsLabel.setShrinkToFit(true);		
//		vsInteter.setBackground(Colour.getInternalColour(44));
		WritableCellFormat vsr = null;
		vsr = new WritableCellFormat(arial10ptBold,NumberFormats.THOUSANDS_FLOAT); 
		vsr.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		vsr.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		vsr.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		vsr.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		vsr.setAlignment(Alignment.LEFT);
		vsr.setVerticalAlignment(VerticalAlignment.CENTRE);
		vsr.setWrap(true);
		vsr.setShrinkToFit(true);				

		
		List<line> list = getDatas();
		if (getSQlresult!=null)
			return Msg.translate(m_ctx, "Отчет: MM_RestOfMaterials: Ошибка запроса.");

		if(list.size() == 0)
			return Msg.translate(m_ctx, "По заданому параметру нет данных");

		String caption = " на период  "
				+ new java.text.SimpleDateFormat("dd.MM.yyyy").format(new Date()) + " г.";

		WritableFont arial10ptBold3 = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
		WritableCellFormat captionStyle = new WritableCellFormat(arial10ptBold3);
		captionStyle.setAlignment(Alignment.CENTRE);
		captionStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		
		label = new Label(0, 1, caption, captionStyle);
		sheet.addCell(label);
		
		
		
		// output datas ----------------------------------{		
		int row = 4;
		for(line ll: list){

			number = new Number(0, row, ll.row, vsInteter);
			sheet.addCell(number);			
			
			number = new Number(1, row, ll.number, vsInteter);
			sheet.addCell(number);
			
			label = new Label(2, row, ll.name, vsr);
			sheet.addCell(label);
			
			label = new Label(3, row, ll.uom, vsLabel);
			sheet.addCell(label);
			
			number = new Number(4, row, ll.qty.doubleValue(), vsLabel);
			sheet.addCell(number);
			
			label = new Label(5, row, ll.werk, vsLabel);
			sheet.addCell(label);
		
			label = new Label(6, row, ll.warehouse, vsLabel);
			sheet.addCell(label);
			
//			number = new Number(7, row, ll.molID, vsInteter);
//			sheet.addCell(number);
			
			label = new Label(7, row, ll.mol, vsLabel);
			sheet.addCell(label);
			
			int l = 0;
			if(ll.name.length() > ll.werk.length())
				l = ll.name.length();
			else
				l = ll.werk.length();
			
			if(l > 21){
				int diff = l / 21;
				if(diff > 1){
					int height = 300 * diff;
					sheet.setRowView(row, height);
				}
					
			}
			
			sheet.setRowView(row, 1000);

			row++;
		}
		
		//output datas -----------------------------------------}
		copy.write();
		copy.close();
		tableBook.close();
		temporaryFile.delete();

		String file;
		if(Env.isWindows())
			 file = Util.localFilePath  + linux +	"MM_RestOfMaterials.xls";
		else
			file = Util.localFilePath  + linux + "MM_RestOfMaterials.xls";		
		
		if(!Env.isWindows())
			Util.launchFile(new File(file));
		else{
			try {
				String command = "excel \"" + file + "\"";
				Runtime.getRuntime().exec("cmd /c start " + command);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}


		return Msg.translate(m_ctx, "Success");
	}


	private List<line> getDatas() {
		List<line> list = new ArrayList<line>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		StringBuilder sql = getSQl(Werk,Locator,Warehouse );

		try {
			pstmt = DB.prepareStatement(sql.toString(), null);

			rs = pstmt.executeQuery();
			while (rs.next()) {
				line l = new line();
				l.row = rs.getInt("row");
				l.number = rs.getInt("m_product_id");
				l.name = rs.getString("name");
				l.uom = rs.getString("uom");
				l.qty = rs.getBigDecimal("qty");
				l.werk = rs.getString("werk");
				l.warehouse = rs.getString("warehouse");
				l.molID = rs.getInt("molID");
				l.mol = rs.getString("molName");
				list.add(l);
			}
		} catch (Exception e) {
			s_log.log(Level.SEVERE, "Report: MM_Materials", e);
			getSQlresult = e.getMessage();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return list;
	}

	private StringBuilder getSQl(BigDecimal werk, BigDecimal locator, BigDecimal warehouse){
		StringBuilder sql = new StringBuilder();
		sql.append(
				"select row_number() over (order by main.m_product_id) as row, main.m_product_id, " + //, (pricelist.\"name\") \"Версия прайс-листа\",\n" +
						"(main.\"name\") \"name\",\n" +
//						"(price.pricestd) \"Стандартная цена\",\n" +
//						"(price.pricelimit) \"Предельная цена\",\n" +
//						"(store.qtyonhand - store.qtyreserved) AS \"Доступно\",\n" +
						"store.qtyonhand qty,\n" +
						"mw.name AS warehouse, mw.m_warehouse_id,\n" +
						"ml.value molName, ml.m_locator_id molID,\n" +
						"org.name werk, org.ad_org_id werkid,\n" +
						"uom.\"name\" uom\n" +
						"from \n" +
						"	adempiere.m_product as main\n" +
						"	left join adempiere.m_product_acct as acct on (main.m_product_id = acct.m_product_id)\n" +
//						"join m_productprice as price on (main.m_product_id = price.m_product_id)\n" +
//						"join m_pricelist_version as pricelist on (pricelist.m_pricelist_version_id = price.m_pricelist_version_id)\n" +
						"	left join adempiere.m_storage as store on (store.m_product_id = main.m_product_id)\n" +
						"	left join adempiere.ad_org as org ON (org.ad_org_id = store.ad_org_id)\n" +
						"	left JOIN adempiere.m_locator ml ON (store.m_locator_id = ml.m_locator_id)\n" +
						"	left JOIN adempiere.m_warehouse mw ON (mw.ad_org_id = org.ad_org_id)\n" +
						"	left join adempiere.c_uom_trl uom on (uom.c_uom_id = main.c_uom_id)\n" +
						"\n" +
						"where\n" +
						"store.m_attributesetinstance_id > 0\n");
		if(werk != null && werk.compareTo(Env.ZERO) == 1)
			sql.append(	"and org.ad_org_id = "+ werk +" ");
		if(warehouse  != null &&  warehouse.compareTo(Env.ZERO) == 1)
			sql.append(	"and mw.m_warehouse_id = "+warehouse+"\n");
		if(locator  != null && locator.compareTo(Env.ZERO) == 1)
			sql.append(	"and ml.m_locator_id = "+locator+"\n");
		
		sql.append(
						"and store.qtyonhand > 0\n" +
						"order by main.m_product_id"				
		);
		return sql;
	}
	
	class line {
		int row = 0;
		int number = 0;
		String name	= "";
		String uom = "";
		BigDecimal qty = Env.ZERO;
		String werk = "";
		String warehouse = "";
		int molID = 0;
		String mol = "";
		
	}
	
}